<?php

namespace app\models;

use yii;
use yii\db\Query;

/**
 *银行支出表
 *
 */
class Bank_bill extends BaseModel
{
    use \app\lib\Redis;

    private $redis;

    public function __construct()
    {
        $this->table = yii::$app->db->tablePrefix . "bank_bill";
    }

    public function all($where = [], $offset = false, $limit = false, $order = false)
    {

    }

    public function one($where = [])
    {
        $db = new Query();
        $db->select('*')->from($this->table);
        if (!empty($where['id'])) {
            $db->andWhere(['id' => $where['id']]);
        }

        if (!empty($where['pid'])) {
            $db->andWhere(['pid' => $where['pid']]);
        }
        return $db->limit(1)->one();
    }

    /**分页总size
     * @param $where
     * @return int
     */
    public function get_bank_bill_size($where)
    {
        $db = (new yii\db\Query)->select('count(id) as num')
            ->from($this->table);
        if ($where['begin_time'] ?? 0) {
            $db->andWhere(['>=', 'date', $where['begin_time']]);
        }
        if ($where['end_time'] ?? 0) {
            $db->andWhere(['<=', 'date', $where['end_time']]);
        }

        if (!empty($where['invocie'])) {
            $db->andWhere(['invocie_num' => $where['invocie']]);
        }
        if (!empty($where['service_name'])) {
            $db->andWhere(['service_name' => $where['service_name']]);
        }
        if (!empty($where['number'])) {
            $db->andWhere(['number' => $where['number']]);
        }
        if (!empty($where['customer_name'])) {
            $db->andWhere(['like', 'customer_name', $where['customer_name']]);
        }
        if (!empty($where['group'])) {
            $db->andWhere(['group' => $where['group']]);
        }
        if ($where['money']) {
            $db->andWhere(['or', ['between', 'out_money', ($where['money'] - 10), ($where['money'] + 10)], ['between', 'in_money', ($where['money'] - 10), ($where['money'] + 10)]]);
        }
        if ($where['type']) {
            if (is_array($where['type'])) {
                $db->andWhere(['in', 'type', $where['type']]);
            } else {
                $db->andWhere(['type' => $where['type']]);
            }
        }
        if ($where['product']) {
            $db->andWhere(['like', 'product', $where['product']]);
        }
        if ($where['bank']) {
            $db->andWhere(['bank' => $where['bank']]);
        }
        if ($where['type_child']) {
            $db->andWhere(['type_child' => $where['type_child']]);
        }
        if ($where['purchase_num']) {
            $db->andWhere(['purchase_num' => $where['purchase_num']]);
        }
        $info = $db->one();
        return $info ? $info['num'] : 0;
    }

    /**分页数据统计
     * @param $where
     * @return array|bool
     */
    public function get_caculate_data($where)
    {
        $db = (new yii\db\Query)->select('SUM(in_money) as in_money,SUM(out_money) as out_money')
            ->from($this->table);
        if ($where['begin_time'] ?? 0) {
            $db->andWhere(['>=', 'date', $where['begin_time']]);
        }
        if ($where['end_time'] ?? 0) {
            $db->andWhere(['<=', 'date', $where['end_time']]);
        }
        if (!empty($where['invocie'])) {
            $db->andWhere(['invocie_num' => $where['invocie']]);
        } else {
            $db->andWhere(['pid' => 0]);
        }
        if (!empty($where['service_name'])) {
            $db->andWhere(['service_name' => $where['service_name']]);
        }
        if (!empty($where['number'])) {
            $db->andWhere(['number' => $where['number']]);
        }
        if (!empty($where['customer_name'])) {
            $db->andWhere(['like', 'customer_name', $where['customer_name']]);
        }
        if (!empty($where['group'])) {
            $db->andWhere(['group' => $where['group']]);
        }
        if ($where['money']) {
            $db->andWhere(['or', ['between', 'out_money', ($where['money'] - 10), ($where['money'] + 10)], ['between', 'in_money', ($where['money'] - 10), ($where['money'] + 10)]]);
        }
        if ($where['type']) {
            $db->andWhere(['type' => $where['type']]);
        }
        if ($where['product']) {
            $db->andWhere(['like', 'product', $where['product']]);
        }
        if ($where['bank']) {
            $db->andWhere(['bank' => $where['bank']]);
        }
        if ($where['type_child']) {
            $db->andWhere(['type_child' => $where['type_child']]);
        }
        if ($where['purchase_num']) {
            $db->andWhere(['purchase_num' => $where['purchase_num']]);
        }
        return $db->one();
    }

    public function getSumByType($where = [])
    {
        $db = new Query();
        $db->select('sum(in_money+out_money) as value,type')->from($this->table);
        if ($where['begin_time'] ?? 0) {
            $db->andWhere(['>=', 'date', $where['begin_time']]);
        }
        if ($where['end_time'] ?? 0) {
            $db->andWhere(['<=', 'date', $where['end_time']]);
        }
        if (!empty($where['invocie'])) {
            $db->andWhere(['invocie_num' => $where['invocie']]);
        } else {
            $db->andWhere(['pid' => 0]);
        }
        if (!empty($where['service_name'])) {
            $db->andWhere(['service_name' => $where['service_name']]);
        }
        if (!empty($where['number'])) {
            $db->andWhere(['number' => $where['number']]);
        }
        if (!empty($where['customer_name'])) {
            $db->andWhere(['like', 'customer_name', $where['customer_name']]);
        }
        if (!empty($where['group'])) {
            $db->andWhere(['group' => $where['group']]);
        }
        if ($where['money']) {
            $db->andWhere(['or', ['between', 'out_money', ($where['money'] - 10), ($where['money'] + 10)], ['between', 'in_money', ($where['money'] - 10), ($where['money'] + 10)]]);
        }
        if ($where['type']) {
            $db->andWhere(['type' => $where['type']]);
        }
        if ($where['product']) {
            $db->andWhere(['like', 'product', $where['product']]);
        }
        if ($where['bank']) {
            $db->andWhere(['bank' => $where['bank']]);
        }
        if ($where['type_child']) {
            $db->andWhere(['type_child' => $where['type_child']]);
        }
        if ($where['purchase_num']) {
            $db->andWhere(['purchase_num' => $where['purchase_num']]);
        }

        return $db->groupBy('type')->all();
    }

    /**分页list
     * @param $p
     * @param $s
     * @param $where
     * @return array
     */
    public function get_bank_bill_list($p, $s, $where)
    {
        $db = new Query();
        $db->select('*')->from($this->table);
        if (!empty($where['invocie'])) {
            $db->andWhere(['invocie_num' => $where['invocie']]);
        }
        if (!empty($where['service_name'])) {
            $db->andWhere(['service_name' => $where['service_name']]);
        }
        if (!empty($where['number'])) {
            $db->andWhere(['number' => $where['number']]);
        }
        if (!empty($where['customer_name'])) {
            $db->andWhere(['like', 'customer_name', $where['customer_name']]);
        }
        if (!empty($where['group'])) {
            $db->andWhere(['group' => $where['group']]);
        }
        if (!empty($where['bank'])) {
            $db->andWhere(['bank' => $where['bank']]);
        }
        if (!empty($where['type_child'])) {
            $db->andWhere(['type_child' => $where['type_child']]);
        }
        if (!empty($where['purchase_num'])) {
            $db->andWhere(['purchase_num' => $where['purchase_num']]);
        }
        if (!empty($where['begin_time'])) {
            $db->andWhere(['>=', 'date', $where['begin_time']]);
        }
        if (!empty($where['end_time'])) {
            $db->andWhere(['<=', 'date', $where['end_time']]);
        }
        if (!empty($where['money'])) {
            $db->andWhere(['or', ['between', 'out_money', ($where['money'] - 10), ($where['money'] + 10)], ['between', 'in_money', ($where['money'] - 10), ($where['money'] + 10)]]);
        }
        if (!empty($where['type'])) {
            if (is_array($where['type'])) {
                $db->andWhere(['in', 'type', $where['type']]);
            } else {
                $db->andWhere(['type' => $where['type']]);
            }
        }
        if (!empty($where['product'])) {
            $db->andWhere(['like', 'product', $where['product']]);
        }

        $list = $db->orderBy(['date' => SORT_DESC, 'pid' => SORT_DESC, 'id' => SORT_ASC])
            ->offset(($p - 1) * $s)->limit($s)->all();
        return $list;
    }

    public function validateBankMoeny($pid)
    {
        $db   = new Query();
        $list = $db->select('*')->from($this->table)->where(['pid' => $pid])->all();
        //$sum1 未拆分 $sum2 拆分
        $sum1   = $sum2   = ['in_money' => 0, 'out_money' => 0];
        $status = 0;
        foreach ($list as $v) {
            if ($v['pid'] == $v['id']) {
                $sum1['in_money']  = $v['in_money'];
                $sum1['out_money'] = $v['out_money'];
            } else {
                $status = 1;
                $sum2['in_money'] += $v['in_money'];
                $sum2['out_money'] += $v['out_money'];
            }
        }
        if (
            $status == 1
            &&
            (
                round($sum1['in_money'], 2) != round($sum2['in_money'], 2)
                ||
                round($sum1['out_money'], 2) != round($sum2['out_money'], 2)

            )
        ) {
            $moneySame = false;
        } else {
            $moneySame = true;
        }
        $this->save(['moneySame' => $moneySame], ['pid' => $pid]);

    }

    /**获取拆分项
     * @param $arr
     * @return array
     */
    public function getListByPid($arr)
    {
        $db = new Query();
        return $db->select('*')->from($this->table)->where(['pid' => $arr])->all();
    }

    /**插入支出记录将pid修改为id
     * @throws yii\db\Exception
     */
    public function updatePid()
    {
        $sql = 'UPDATE `jingsourcing_bank_bill` SET pid=id WHERE pid=0';
        yii::$app->db->createCommand($sql)->execute();
    }

    /**
     * @param $data
     * @return int
     * @throws yii\db\Exception
     */
    public function insert_bank_bill($data)
    {
        $keys =
            [
            'date', 'in_money', 'out_money', 'remain_money', 'country', 'number',
            'customer_name', 'service_name', 'type', 'product', 'account', 'buyer',
            'invocie_num', 'group', 'bank', 'purchase_num',
        ];
        return yii::$app->db->createCommand()
            ->batchInsert(yii::$app->db->tablePrefix . "bank_bill", $keys, $data)
            ->execute();
    }

    public function get_money_by_invoice($invoice_arr)
    {
        $query = new Query();
        $query->select('in_money,out_money,invocie_num,number')
            ->from($this->table)
            ->where(['in', 'invocie_num', $invoice_arr]);
        $query->andWhere(['in', 'type', [1, 11]]);
        $db   = new Query();
        $list = $db->select('sum(in_money) as in_money,sum(out_money) as out_money,invocie_num,number')
            ->from(['a' => $query])
            ->groupBy('invocie_num')
            ->all();
        return $list;
    }

    public function get_others_by_invoice($in)
    {
        $db = new Query();
        $db->select('sum(in_money) as in_money,sum(out_money) as out_money,invocie_num,number')->from($this->table);
        $db->andWhere(['in', 'type', [5, '7', 20, 13, 17]]);
        $db->andWhere(['in', 'invocie_num', $in]);
        $list = $db->groupBy('invocie_num')->all();
        return $list;

    }

    //获取客户消费金额
    public function get_shopping($customer_num)
    {
        $db   = new Query();
        $list = $db->select('number,sum(in_money) as in_money,sum(out_money) as out_money')
            ->from(yii::$app->db->tablePrefix . "bank_bill")
            ->andWhere(['type' => 2])
            ->andWhere(['in', 'number', $customer_num])
            ->groupBy('number')
            ->all();
        $arr = [];
        foreach ($list as $value) {
            $arr[$value['number']] = $value;
        }
        return $arr;
    }

    public function get_bank_info_by_in($in)
    {
        $db   = new Query();
        $list = $db->select('*')
            ->from($this->table . ' as a')
            ->join('left join', yii::$app->db->tablePrefix . 'bank_type as b', 'a.type=b.id')
            ->where(['invocie_num' => $in])
            ->orderBy(['date' => SORT_DESC])
            ->all();
        return $list;
    }

    //银行账单类型
    public function get_bank_type()
    {
        $key         = "bank_type";
        $this->redis = $this->getInstance();
        if ($list = $this->redis->get($key)) {
            return json_decode($list, true);
        }

        $db   = new Query();
        $list = $db->select('*')
            ->from(yii::$app->db->tablePrefix . 'bank_type')
            ->all();
        $this->redis->setex($key, 3600 * 24, json_encode($list));
        return $list;
    }

    public function month_pay($year)
    {
        $sql = "SELECT * FROM (
            SELECT
            sum(in_money) AS in_money,sum(out_money) AS out_money,DATE_FORMAT(date,'%Y-%m') AS date ,type
            FROM `jingsourcing_bank_bill`
            WHERE DATE_FORMAT(date,'%Y')={$year}
            GROUP BY  DATE_FORMAT(date,'%Y-%m'),type
            ORDER BY  date DESC,type ASC
            ) a
            LEFT JOIN  jingsourcing_bank_type AS b ON a.type=b.id";
        $list = yii::$app->db->createCommand($sql)->queryAll();
        return $list;
    }

    //一达通农行结余信息
    public function bank_remain($m)
    {
        $begin = $m . '-01';
        $end   = $m . '-31';
        $query = new Query();
        $query->select('max(id)')
            ->from($this->table)
            ->where(['between', 'date', $begin, $end])
            ->groupBy("bank,date");
        $db   = new Query();
        $list = $db->select('*')
            ->from($this->table)
            ->where(['between', 'date', $begin, $end])
            ->andWhere(['in', 'id', $query])
            ->orderBy('bank ASC,date DESC')
            ->all();
        $output = [];
        foreach ($list as $v) {
            if ($v['bank'] == 1) {
                $output[1][$v['date']] = $v;
            } elseif ($v['bank'] == 2) {
                $output[2][$v['date']] = $v;
            }
        }
        return $output;
    }

    /**
     *
     * @param type 类型
     * @param month 月份
     * */
    public function get_info_by_type($type = [], $month)
    {
        $begin         = $month . '-01';
        $end           = $month . '-31';
        $year_begin    = date('Y', strtotime($month)) . '-01-01';
        $year_end      = date('Y-m', strtotime($month)) . '-31';
        $info          = [];
        $info['month'] = $this->get_info($type, $begin, $end);
        $info['year']  = $this->get_info($type, $year_begin, $year_end);
        return $info;
    }

    public function get_info($type, $begin, $end)
    {
        $db = new Query();
        $db->select('sum(in_money) as in_money,sum(out_money) as out_money')->from($this->table);
        if (!empty($type)) {
            $db->andWhere(['in', 'type', $type]);
        }
        $db->andWhere(['pid' => 0]);
        return $db->andWhere(['between', 'date', $begin, $end])
            ->one();
    }

    /**
     *获取采购编号对应已支付金额
     */
    public function getPurchaseNumPay($arr, $filed = '*')
    {
        $db = new Query();
        $db->select($filed)->from($this->table);
        if (is_array($arr)) {
            $db->andWhere(['in', 'purchase_num', $arr]);
        } else {
            $db->andWhere(['purchase_num' => $arr]);
        }
        return $db->all();
    }

    public function sumMoneyEveryMonth($year)
    {
        $db   = new Query();
        $list = $db->select(['sum(`out_money`-`in_money`) as money', "DATE_FORMAT(date,'%Y-%m') AS m"])
            ->from($this->table)
            ->where('`pid`=`id`')
            ->andWhere(['>=', 'date', $year . '-01-01'])
            ->andWhere(['<=', 'date', $year . '-12-31'])
            ->andWhere(['!=', 'type', 8])
            ->groupBy('m')
            ->all();
        return $list;
    }

    public function sumMoneyByMonth($month)
    {
        $db   = new Query();
        $list = $db->select(['sum(`out_money`-`in_money`) as money', 'type'])
            ->from($this->table)
            ->where('`pid`=`id`')
            ->andWhere(['>=', 'date', $month . '-01'])
            ->andWhere(['<=', 'date', $month . '-31'])
            ->groupBy('type')
            ->all();
        return $list;
    }

}
